﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace Net.Sz.Framework.ExcelTools.CreateCode.excel
{
    /// <summary>
    /// 读取一堆 Excel 文件的 每一个 Excel 的第一个sheet
    /// </summary>
    public class ExcelDatas
    {
        /// <summary>
        /// 存储类型和文件名对应的
        /// </summary>
        public Dictionary<string, HashSet<string>> SheetName_And_FileName = new Dictionary<string, HashSet<string>>();

        /// <summary>
        /// 存储类型和文件名对应的
        /// </summary>
        public Dictionary<string, HashSet<string>> FileName_And_SheetName = new Dictionary<string, HashSet<string>>();

        /// <summary>
        ///  key：  Excel 文件名
        ///  <para>value： Excel 文件对应的数据</para>
        /// </summary>
        public Dictionary<string, ExcelData> Datas = new Dictionary<string, ExcelData>();

        static public ExcelDatas ReadExcelDataToCache(List<string> excelPaths, Action<string> show, bool isHebing = false)
        {

            if (excelPaths == null || excelPaths.Count == 0)
            {
                return null;
            }

            Dictionary<string, HashSet<string>> filename_and_key = new Dictionary<string, HashSet<string>>();

            ExcelDatas exceldatas = new ExcelDatas();

            foreach (var excelPath in excelPaths)
            {
                ExcelData exceldata = new ExcelData();

                if (exceldata.Read(excelPath, show))
                {
                    string datakey = Path.GetFileNameWithoutExtension(excelPath);
                    if (!exceldatas.Datas.ContainsKey(datakey))
                    {
                        //存储数据
                        exceldatas.Datas.Add(datakey, exceldata);
                    }
                    else
                    {
                        foreach (var row in exceldata.Rows)
                        {
                            if (exceldatas.Datas[datakey].Rows.ContainsKey(row.Value.AtKey))
                            {
                                show("文件：" + Path.GetFileName(excelPath) + " 存在重复主键 " + row.Value.AtKey);
                                return null;
                            }
                            exceldatas.Datas[datakey].Rows.Add(row.Value.AtKey, row.Value);
                        }
                    }
                    {
                        //存储模型
                        if (!exceldatas.SheetName_And_FileName.ContainsKey(exceldata.SheetName))
                        {
                            exceldatas.SheetName_And_FileName.Add(exceldata.SheetName, new HashSet<string>());
                        }

                        exceldatas.SheetName_And_FileName[exceldata.SheetName].Add(datakey);

                        //存储模型
                        if (!exceldatas.FileName_And_SheetName.ContainsKey(datakey))
                        {
                            exceldatas.FileName_And_SheetName.Add(datakey, new HashSet<string>());
                        }
                        exceldatas.FileName_And_SheetName[datakey].Add(exceldata.SheetName);
                    }
                    if (!exceldata.SheetName.Equals(datakey))
                    {
                        {
                            //存储数据
                            if (!exceldatas.Datas.ContainsKey(exceldata.SheetName))
                            {
                                exceldatas.Datas[exceldata.SheetName] = new ExcelData() { SheetName = exceldata.SheetName };
                            }
                            foreach (var row in exceldata.Rows)
                            {
                                if (exceldatas.Datas[exceldata.SheetName].Rows.ContainsKey(row.Key))
                                {
                                    show("文件：" + Path.GetFileName(excelPath) + " 存在重复主键 " + row.Key);
                                    return null;
                                }
                                exceldatas.Datas[exceldata.SheetName].Rows.Add(row.Key, row.Value);
                            }
                        }
                    }
                }
                else
                {
                    return null;
                }
            }
            if (exceldatas.Datas.Count > 0)
            {
                return exceldatas;
            }
            else
            {
                return null;
            }
        }
    }

    /// <summary>
    ///  单个 Excel 文件的第一个sheet
    /// </summary>
    public class ExcelData
    {
        public string SheetName = null;
        public string FileName = null;

        /// <summary>
        /// 数据行,key 为数据的主键
        /// </summary>
        public Dictionary<String, ExcelDataRow> Rows = new Dictionary<String, ExcelDataRow>();

        public static String ReplaceSideLine(String a)
        {
            if (string.IsNullOrWhiteSpace(a)) { return ""; }
            else
            {
                if (a.ToLower() == "class")
                {
                    a = "Clazz";
                }
                return a.Replace("-", "_");
            }
        }

        /// <summary>
        /// 去掉所有下划线
        /// </summary>
        /// <param name="a"></param>
        /// <returns></returns>
        public static String ReplaceSideLine2Coder(String str)
        {
            str = str.Replace("_", "");
            return str;
        }

        /// <summary>
        /// 首字母小写
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static String FirstLower(String str)
        {
            char[] chars = str.ToCharArray();
            chars[0] = Char.ToLower(chars[0]);
            str = new String(chars);
            return str;
        }
        /// <summary>
        /// 首字母大写
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static String FirstUpper(String str)
        {
            char[] chars = str.ToCharArray();
            chars[0] = Char.ToUpper(chars[0]);
            str = new String(chars);
            return str;
        }


        /// <summary>
        /// 根据excel 解析数据类型
        /// </summary>
        /// <param name="titleType">列类型</param>
        /// <param name="title">列头</param>
        /// <param name="notes">注释</param>
        /// <param name="guishu">归属</param>
        /// <param name="data">数据</param>
        /// <param name="row">返回值</param>
        /// <returns></returns>
        bool GetExcelPropertyType(string filename, ICell titleType, ICell notes, ICell data, ref ExcelData.ExcelDataRow row)
        {
            if (titleType == null || string.IsNullOrWhiteSpace(titleType.ToString()))
            {
                return false;
            }

            string stringCellValue = titleType.StringCellValue;
            string[] vs = stringCellValue.Split('=');
            string fieldType = titleType.StringCellValue.ToLower();
            string fieldName = vs[0];
            string fieldNotes = notes == null ? "" : notes.ToString();
            string fieldGuishu = titleType == null ? "" : titleType.ToString().ToLower();
            if (fieldGuishu.IndexOf("=hl") >= 0)
            {
                /*不要的备注*/
                return false;
            }
            ExcelData.ExcelDataRow.ExcelDataCell cell = new ExcelData.ExcelDataRow.ExcelDataCell();
            cell.CellName = ReplaceSideLine(fieldName).Trim();
            cell.CellName2Coder = ReplaceSideLine2Coder(cell.CellName);
            cell.CellName2CoderUpper = FirstUpper(cell.CellName2Coder);
            cell.CellNotes = fieldNotes.Replace("\n", "").Replace("\r", "");

            if (fieldType.IndexOf("[]") >= 0 || fieldType.IndexOf("=s") >= 0)
            {
                cell.CellValueType = "String";
                cell.CellLength = 50000;
                if (fieldType.IndexOf("=s_") >= 0)
                {
                    foreach (var item in vs)
                    {
                        string v2 = item.ToLower();
                        if (v2.IndexOf("s_") >= 0)
                        {
                            string lenStr = v2.Replace("s_", "");
                            cell.CellLength = int.Parse(lenStr);
                        }
                    }
                }
            }
            else if (fieldType.IndexOf("=b") >= 0)
            {
                //bool
                cell.CellValueType = "Boolean";
            }
            else if (fieldType.IndexOf("=l") >= 0)
            {
                cell.CellValueType = "long";
            }
            else if (fieldType.IndexOf("=fl") >= 0)
            {
                cell.CellValueType = "float";
            }
            else if (fieldType.IndexOf("=d") >= 0)
            {
                //double
                cell.CellValueType = "double";
            }
            else if (fieldType.IndexOf("=i") >= 0)
            {
                //double
                cell.CellValueType = "int";
            }
            else
            {
                cell.CellValueType = "int";
            }


            cell.CellValue = CellValue(cell.CellValueType, data).ToString();

            //cell.CellValue = GetCellValue<string>(data);
            //去除前置后置空格，去除换行符
            cell.CellValue = cell.CellValue.Replace("\n", "").Replace("\r", "").Replace("'", "\\\'").Trim();
            if (!cell.CellValueType.ToUpper().Equals("STRING"))
            {
                if (string.IsNullOrWhiteSpace(cell.CellValue))
                {
                    cell.CellValue = "0";
                }
            }

            //标识主键
            if ("id".Equals(cell.CellName.ToLower()) || (fieldGuishu.IndexOf("=p") >= 0))
            {
                cell.IsPKey = true;
                cell.CellLength = 100;
                row.AtKey = cell.CellValue;
            }

            cell.Cells = "ALL";

            if (fieldGuishu.IndexOf("client") >= 0 || (fieldGuishu.IndexOf("=ac") >= 0))
            {
                cell.Cells = "AC";
            }
            else if (fieldGuishu.IndexOf("server") >= 0 || (fieldGuishu.IndexOf("=as") >= 0))
            {
                cell.Cells = "AS";
            }
            else
            {
                cell.Cells = "ALL";
            }

            if (row.Cells.ContainsKey(cell.CellName))
            {
                MessageBox.Show("配置表：" + filename + ", 存在相同的字段名：" + cell.CellName, "错误提示");
                throw new Exception("存在相同的字段名：" + cell.CellName);
            }
            row.Cells.Add(cell.CellName, cell);
            return true;
        }

        bool IsNullOrWhiteSpace(object obj)
        {
            if (obj == null)
            {
                return true;
            }
            return string.IsNullOrWhiteSpace(obj.ToString());
        }

        public static bool IsNumeric(string value)
        {
            return Regex.IsMatch(value, @"^[+-]?\d*[.]?\d*$");
        }

        private string CellValue(string type, ICell cell)
        {
            object v = CellValue(cell);
            object revert = string.Empty;
            try
            {
                switch (type)
                {
                    case "bool":
                        if (IsNullOrWhiteSpace(v))
                            revert = false;
                        else
                            revert = bool.Parse(v.ToString());
                        break;
                    case "byte":
                        if (IsNullOrWhiteSpace(v))
                            revert = 0;
                        else
                            revert = ((byte)double.Parse(v.ToString()));
                        break;
                    case "short":
                        if (IsNullOrWhiteSpace(v))
                            revert = 0;
                        else
                            revert = ((short)double.Parse(v.ToString()));
                        break;
                    case "int":
                        if (IsNullOrWhiteSpace(v))
                            revert = 0;
                        else
                            revert = ((int)double.Parse(v.ToString()));
                        break;
                    case "long":
                        if (IsNullOrWhiteSpace(v))
                            revert = 0;
                        else
                            revert = ((long)double.Parse(v.ToString()));
                        break;
                    case "float":
                        if (IsNullOrWhiteSpace(v))
                            revert = 0;
                        else
                            revert = ((float)double.Parse(v.ToString()));
                        break;
                    default:
                        if (IsNullOrWhiteSpace(v))
                            revert = "";
                        else
                        {
                            revert = v.ToString();
                            if (IsNumeric(revert.ToString()))
                            {
                                revert = ((long)double.Parse(revert.ToString()));
                            }
                        }
                        break;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(v);
                Console.WriteLine(e);
            }
            return revert.ToString();
        }

        private string CellValue(ICell item)
        {
            if (item == null)
            {
                return string.Empty;
            }
            switch (item.CellType)
            {
                case CellType.Boolean:
                    return item.BooleanCellValue.ToString();

                case CellType.Error:
                    return ErrorEval.GetText(item.ErrorCellValue);

                case CellType.Formula:
                    switch (item.CachedFormulaResultType)
                    {
                        case CellType.Boolean:
                            return item.BooleanCellValue.ToString();

                        case CellType.Error:
                            return ErrorEval.GetText(item.ErrorCellValue);

                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(item))
                            {
                                return item.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                return item.NumericCellValue.ToString();
                            }
                        case CellType.String:
                            string str = item.StringCellValue;
                            if (!string.IsNullOrEmpty(str))
                            {
                                return str.ToString();
                            }
                            else
                            {
                                return string.Empty;
                            }
                        case CellType.Unknown:
                        case CellType.Blank:
                        default:
                            return string.Empty;
                    }
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(item))
                    {
                        return item.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        return item.NumericCellValue.ToString();
                    }
                case CellType.String:
                    string strValue = item.StringCellValue;
                    return strValue.ToString().Trim();

                case CellType.Unknown:
                case CellType.Blank:
                default:
                    return string.Empty;
            }
        }

        public bool Read(string excelPath, Action<string> show)
        {
            try
            {
                using (FileStream fs = File.OpenRead(excelPath))   //打开myxls.xls文件
                {
                    IWorkbook wk;
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    if (excelPath.IndexOf(".xlsx") >= 0)
                    {
                        wk = new XSSFWorkbook(fs);
                    }
                    else if (excelPath.IndexOf(".xls") >= 0)
                    {
                        wk = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        wk = null;
                    }
                    if (wk.NumberOfSheets > 0)
                    {
                        var table = wk.GetSheetAt(0);
                        this.SheetName = ReplaceSideLine(table.SheetName).ToLower().Trim();
                        this.FileName = Path.GetFileName(excelPath);
                        string filename = Path.GetFileName(excelPath);
                        show("开始分析文件：" + filename + " 文件数据");
                        if (table != null)
                        {
                            if (table.LastRowNum < 2)
                            {
                                show("文件格式是第一行是字段类型，第二行字段命名，第三行是注释，第四行是归属");
                                return false;
                            }

                            IRow rowType = table.GetRow(0);  //字段名字行和类型型行
                            IRow rowNontes = table.GetRow(1);  //读取配置说明

                            if (table.LastRowNum > 2)
                            {
                                for (int i = 2; i <= table.LastRowNum; i++)
                                {
                                    IRow rowData = table.GetRow(i); //读取当前行数据
                                    if (rowData != null)
                                    {
                                        // 处理如果该行为空值那么忽略
                                        // todo 考虑怎么添加
                                        for (int k = 0; k < rowType.LastCellNum; k++)
                                        {
                                            ICell cell2 = rowData.GetCell(k);  //当前表格
                                            if (cell2 != null && !string.IsNullOrWhiteSpace(cell2.ToString())) { goto lab_xx; }
                                        }
                                        return true;
                                    lab_xx:
                                        ExcelData.ExcelDataRow datarow = new ExcelData.ExcelDataRow();
                                        for (int k = 0; k < rowType.LastCellNum; k++)  //LastCellNum 是当前行的总列数
                                        {
                                            try
                                            {
                                                GetExcelPropertyType(filename, rowType.GetCell(k), rowNontes.GetCell(k), rowData.GetCell(k), ref datarow);
                                            }
                                            catch (Exception ex)
                                            {
                                                show("文件：" + Path.GetFileName(excelPath) + " 取值错误 第 " + (i + 1) + " 行 " + " 第 " + (k + 1) + " 列 " + ex.ToString());
                                                return false;
                                            }
                                        }
                                        if (string.IsNullOrWhiteSpace(datarow.AtKey))
                                        {
                                            show("文件：" + Path.GetFileName(excelPath) + " 缺少主键");
                                            return false;
                                        }
                                        if (Rows.ContainsKey(datarow.AtKey))
                                        {
                                            show("文件：" + Path.GetFileName(excelPath) + " 存在重复主键 " + datarow.AtKey + " 第 " + (i + 1) + " 行 ");
                                            return false;
                                        }

                                        if (datarow.Cells.Values.Where(l => l.IsPKey).Count() > 1)
                                        {
                                            show("文件：" + Path.GetFileName(excelPath) + " 不支持双主键设置");
                                            return false;
                                        }
                                        Rows.Add(datarow.AtKey, datarow);
                                    }
                                }
                            }
                            return true;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                show("请确保是2003或者wps创建的excel文件并且处于关闭状态：" + Path.GetFileName(excelPath));
                Console.WriteLine(e);
            }
            return false;
        }

        /// <summary>
        /// 数据行
        /// </summary>
        public class ExcelDataRow
        {
            class ExcelCellComparer : IComparer<ExcelDataCell>
            {
                public int Compare(ExcelDataCell x, ExcelDataCell y)
                {
                    return string.Compare(x.CellName, y.CellName, true);
                }
            }

            /// <summary>
            /// 数据块
            /// </summary>
            public class ExcelDataCell
            {
                /// <summary>
                /// 标识字段的归属 ALL=客户端和服务器通用 AC=表示客户端使用 AS=表示服务使用
                /// </summary>
                public string Cells { get; set; }
                /// <summary>
                /// 注释
                /// </summary>
                public string CellNotes { get; set; }

                /// <summary>
                /// Excel节点名称
                /// </summary>
                public string CellName { get; set; }
                /// <summary>
                /// 最后生成的字段名字
                /// </summary>
                public string CellName2Coder { get; set; }
                /// <summary>
                /// 最后生成的字段名字,首字母大写
                /// </summary>
                public string CellName2CoderUpper { get; set; }
                private string cellvalue = null;
                /// <summary>
                /// 值
                /// </summary>
                public string CellValue
                {
                    get
                    {
                        if (cellvalue.EndsWith(".0"))
                        {
                            cellvalue = cellvalue.Substring(0, cellvalue.Length - 2);
                        }
                        return cellvalue;
                    }
                    set
                    {
                        cellvalue = value;
                        if (cellvalue.EndsWith(".0"))
                        {
                            cellvalue = cellvalue.Substring(0, cellvalue.Length - 2);
                        }
                    }
                }

                /// <summary>
                /// 属性类型 int，float
                /// </summary>
                public string CellValueType { get; set; }

                /// <summary>
                /// 长度，，如字符串类型 在数据需要设置长度 默认 255
                /// </summary>
                public int CellLength { get; set; }

                /// <summary>
                /// 是否是唯一键
                /// </summary>
                public bool IsPKey { get; set; }

                /// <summary>
                /// 关联excel文件
                /// </summary>
                public string CellFileName { get; set; }


                public string ToXmlString(string cellgs, bool is_Not_Null)
                {
                    if (Cells.Equals("ALL") || cellgs.Equals("ALL") || Cells.Equals(cellgs))
                    {
                        if (is_Not_Null)
                        {
                            if (string.IsNullOrWhiteSpace(CellValue) || "0".Equals(CellValue))
                            {
                                return null;
                            }
                        }
                        return " " + CellName + "=\"" + CellValue + "\"";
                    }
                    return "";
                }

                public string ToMysqlString(string cellgs)
                {
                    StringBuilder sbuilder = new StringBuilder();
                    if (Cells.Equals("ALL") || cellgs.Equals("ALL") || Cells.Equals(cellgs))
                    {
                        sbuilder.Append(" `" + CellName + "` ");
                        switch (CellValueType.ToLower())
                        {
                            case "int":
                                sbuilder.Append("int ");
                                break;
                            case "boolean":
                                sbuilder.Append(" TINYINT(1) ");
                                break;
                            case "double":
                                sbuilder.Append(" double ");
                                break;
                            case "float":
                                sbuilder.Append(" float ");
                                break;
                            case "string":

                                if (CellLength < 500)
                                {
                                    sbuilder.Append(" VARCHAR(" + CellLength + ") ");
                                }
                                else if (CellLength < 10000)
                                {
                                    sbuilder.Append(" TEXT ");
                                }
                                else
                                {
                                    sbuilder.Append(" LONGTEXT ");
                                }
                                break;
                            case "long":
                                sbuilder.Append(" bigint ");
                                break;
                        }
                        if (IsPKey)
                        {
                            sbuilder.Append(" NOT NULL primary key ");
                        }
                        sbuilder.Append(" COMMENT '" + CellNotes + "'");
                    }
                    return sbuilder.ToString();
                }
            }

            public Dictionary<string, ExcelDataCell> Cells = new Dictionary<string, ExcelDataCell>();


            /// <summary>
            /// 主键，唯一键
            /// </summary>
            public string AtKey { get; set; }

            public string ToXmlString(string cellgs, bool is_Not_Null)
            {
                StringBuilder xmlBuilder = new StringBuilder();
                foreach (var item in Cells)
                {
                    string tmp = item.Value.ToXmlString(cellgs, is_Not_Null);
                    if (!string.IsNullOrWhiteSpace(tmp))
                    {
                        xmlBuilder.Append(tmp);
                    }
                }
                return xmlBuilder.ToString();
            }


            /// <summary>
            /// 生成表头
            /// </summary>
            /// <returns></returns>
            public string ToMysqlString(string cellgs)
            {
                StringBuilder xmlBuilder = new StringBuilder();
                bool isdouhao = false;
                var tmp = Cells.Values.ToArray();
                for (int i = 0; i < tmp.Length; i++)
                {
                    string mysql = tmp[i].ToMysqlString(cellgs);
                    if (!string.IsNullOrWhiteSpace(mysql))
                    {
                        if (isdouhao) { xmlBuilder.AppendLine(","); }
                        else { xmlBuilder.AppendLine(""); }
                        xmlBuilder.Append("  " + mysql);
                        isdouhao = true;
                    }
                }
                return xmlBuilder.ToString();
            }


            public override string ToString()
            {
                return base.ToString();
            }

        }

    }
}
